﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using LS.FrmUI.Common;
using System.Data.SQLite;
using System.Data.Common;
using LS.FrmUI.Model;
using System.IO;

namespace LS.FrmUI.Dal
{
    public class AttDal
    {
        public bool ImportToDBFromFile(string connStr, string fullExcelFilePath)
        {
            string sql = @"insert into AttachMent(KITID,CName,CPhone,Ptype,ISN,DateTime,Money,JName,JNameError,Sort,TimeStamp) 
                                values ('{0}','{1}','{2}','{3}','{4}','{5}',{6},'{7}','{8}',{9},'{10}')";
            //过滤字判断
            string fifterPath = Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, "System.Data.Words.dll");
            List<string> words = File.ReadAllLines(fifterPath).ToList().Distinct().ToList();
            //过滤字判断
            string tempSQL = string.Empty;

            using (SQLiteConnection conn = new SQLiteConnection(connStr))
            {
                conn.Open();
                DbTransaction trans = conn.BeginTransaction();
                DataSet ds = OfficeUtils.ExcelToDataSetByGjp(fullExcelFilePath);
                DataTable dt = ds.Tables[0];
                if (dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        //插入数据库的时候就过滤关键词
                        string fileJname = Convert.ToString(dr["JName"]);
                        string fileJnameError = Convert.ToString(dr["JNameError"]);

                        foreach (string w in words)
                        {
                            if (fileJname.StartsWith(w))
                            {
                                fileJname = fileJname.Replace(w, "").TrimStart();
                                break;
                            }

                            if (fileJnameError.StartsWith(w))
                            {
                                fileJnameError = fileJnameError.Replace(w, "").TrimStart();
                                break;
                            }
                        }


                        tempSQL = string.Format(sql,
                            Convert.ToString(dr["KITID"])
                            , Convert.ToString(dr["CName"])
                            , Convert.ToString(dr["CPhone"])
                            , Convert.ToString(dr["PType"])
                            , Convert.ToString(dr["ISN"])
                            , Convert.ToString(dr["DateTime"])
                            , dr["Money"] == DBNull.Value ? 0.0 : Convert.ToDouble(dr["Money"])
                            , fileJname//Convert.ToString(dr["JName"])
                            , fileJnameError
                            , Convert.ToInt32(dr["Sort"])
                            , DateTime.Now
                            );
                        using (SQLiteCommand comm = new SQLiteCommand(tempSQL, conn))
                        {
                            comm.ExecuteNonQuery();
                        }
                    }
                }
                trans.Commit();
            }
            //导入完成后，更新字段。
            string updateSQL = @"update attachment set JName = JNameError where JName =''";
            DBHelper.ExecuteNonQuery(updateSQL);

            return true;
        }

        /// <summary>
        /// 今天的数据是否导入,导入为真，没导入为假。
        /// </summary>
        /// <returns></returns>
        public bool IsHasImport()
        {
            string sql = @"select * from attachment
where timestamp like '%{0}%' or timestamp like '%{1}%'";
            string year = System.DateTime.Now.Year.ToString();
            string mouth = System.DateTime.Now.Month.ToString();
            string day = System.DateTime.Now.Day.ToString();
            sql = string.Format(sql, year + "-" + mouth + "-" + day, year + "/" + mouth + "/" + day);
            DataTable dt = DBHelper.GetTable(sql);
            if (dt.Rows.Count > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public List<Attachment> GetResByAddress(string addRess = "ALL")
        {
            string sql = string.Empty;
            DataTable dt = new DataTable();

            if (addRess == "ALL")
            {
                sql = @"select *,sum(money) as allMoney from Attachment 
group by kitid";
            }
            if (addRess != "ALL" && addRess != "其他")
            {
                sql = @"select *,sum(money) as allMoney from Attachment 
where JName like '" + addRess + "%' group by kitid";
            }
            if (addRess == "其他")
            {
                sql = @"select *,sum(money) as allMoney from attachment where JName not like '%其他随便写%' " + GetOtherSQL() + " group by kitid";
            }

            dt = DBHelper.GetTable(sql);
            List<Attachment> lst = new List<Attachment>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    Attachment info = new Attachment();
                    LoadEntity(dr, info);
                    lst.Add(info);
                }
            }
            return lst;
        }

        public DataTable GetResByKITIDAndISN(string kitid, string cName, string cPhone, string isn, string PType)
        {
            string sql = @"select *,sum(money) as allMoney from Attachment 
where kitid like '%{0}%' 
and CName like '%{1}%'
and cPhone like '%{2}%'
and ISN like '%{3}%'
and PType  like '%{4}%'
group by kitid  ";
            return DBHelper.GetTable(string.Format(sql, kitid, cName, cPhone, isn, PType));
        }
        private void LoadEntity(DataRow dr, Attachment info)
        {
            info.KITID = dr["KITID"] == DBNull.Value ? string.Empty : Convert.ToString(dr["KITID"]);
            info.CName = dr["CName"] == DBNull.Value ? string.Empty : Convert.ToString(dr["CName"]);
            info.CPhone = dr["CPhone"] == DBNull.Value ? string.Empty : Convert.ToString(dr["CPhone"]);
            info.PType = dr["PType"] == DBNull.Value ? string.Empty : Convert.ToString(dr["PType"]);
            if (info.PType.Length > 6)
            {
                info.PType = info.PType.Substring(0, 6);
            }
            info.ISN = dr["ISN"] == DBNull.Value ? string.Empty : Convert.ToString(dr["ISN"]);
            info.DateTime = string.IsNullOrEmpty(Convert.ToString(dr["DateTime"])) == false ? Convert.ToDateTime(dr["DateTime"]) : new DateTime();
            info.Money = dr["Money"] == DBNull.Value ? 0.0 : Convert.ToDouble(dr["Money"]);
            info.JName = string.IsNullOrEmpty(Convert.ToString(dr["JName"])) == false ? Convert.ToString(dr["JName"]) : Convert.ToString(dr["JNameError"]);
            info.Sort = Convert.ToInt32(dr["Sort"]);
            info.TimeStamp = Convert.ToDateTime(dr["TimeStamp"]);
            info.allMoney = dr["allMoney"] == DBNull.Value ? 0.0 : Convert.ToDouble(dr["allMoney"]);
        }
        /// <summary>
        /// 获取其他地区的SQL
        /// </summary>
        /// <returns></returns>
        private string GetOtherSQL()
        {
            StringBuilder sb = new StringBuilder();
            AreaDal areaDal = new AreaDal();

            var list = areaDal.GetAllArea();
            list.ForEach(x =>
            {
                sb.AppendFormat(" and JName not like '{0}%' ", x.Trim());
            });
            //DataTable dt = DBHelper.GetTable("select * from areasetting");
            //if (dt.Rows.Count > 0)
            //{
            //    foreach (DataRow dr in dt.Rows)
            //    {
            //    }
            //}
            return sb.ToString();
        }
        public bool ClearData()
        {
            string sql = "delete from Attachment";
            int res = DBHelper.ExecuteNonQuery(sql);
            if (res == 0)
            {
                DataTable dt = DBHelper.GetTable("select * from attachment");
                if (dt.Rows.Count > 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }

            }
            else
            {
                return true;
            }
        }
    }
}
